package com.neusoft.chenhanhua.gqing;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

public class Pms_album21 {
	//插入
			@Test
			public void insert() {
				Connection conn=null;
				//准备数据库用户名，密码，url，driver
				String username="root";//用户名
				String password="root";//密码
				String driver="com.mysql.jdbc.Driver";//driver
				String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";//url
				try {
					//加载驱动类到虚拟机中
					Class.forName(driver);//这是属于反射的内容
					//获取连接java.sql.Connection
					conn=DriverManager.getConnection(url,username,password);
				} catch (Exception e) {
					e.printStackTrace();
				}
				//准备SQL命令
				String sql="insert into Pms_album(id,name,cover_pic,pic_count,sort,description)values(90,9,2,6,5,9)";
				Statement st=null;
				try {
					//通过数据库连接conn获取事务java.sql.Statement对象st
					st=conn.createStatement();
					//执行sql命令
					int row=st.executeUpdate(sql);
					System.out.println(row>0?"添加成功":"添加失败");
				} catch (SQLException e) {
					e.printStackTrace();
				}finally {
					if (st!=null) {
						try {
							st.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					if (conn!=null) {
						try {
							st.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				}
			}
			@Test 
			public void update() {
				Connection conn=null;
				//准备数据库用户名,密码，url，driver
				String username="root";
				String password="root";
				String driver="com.mysql.jdbc.Driver";
				String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
				try {
					//加载驱动类到虚拟机中
					Class.forName(driver);
					//获取数据库连接java.sql.Connection
					conn=DriverManager.getConnection(url,username,password);
				} catch (Exception e) {
					e.printStackTrace();
				}
				//准备SQL命令
				String sql="update Pms_album set name=5 where id=2";
				Statement st=null;
				try {
					//通过数据库连接conn获取事务java.sql.staement
					st=conn.createStatement();
					int row=st.executeUpdate(sql);
					System.out.println(row>0?"修改成功":"修改失败");
				} catch (SQLException e) {
					e.printStackTrace();
				}finally {
					if (st!=null) {
						try {
							st.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					if (conn!=null) {
						try {
							conn.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				}
				
			}
			@Test
			public void delete() {
				Connection conn=null;
				//准备数据库用户名，密码，url，driver
				String username="root";
				String password="root";
				String driver="com.mysql.jdbc.Driver";
				//协议：//地址：端口号/数据库？连接属性设置
				String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
				try {
					//加载驱动类到虚拟机中，这是属于反射的内容
					Class.forName(driver);
					//获取数据库连接java.sql.Connection
					conn=DriverManager.getConnection(url,username,password);
				} catch (Exception e) {
					e.printStackTrace();
				}
				//准备SQL命令
				String sql="delete from Pms_album where id=1";
				Statement st=null;
				try {
					//通过数据库连接conn获取事务java.sql.Statement对象st
					st=conn.createStatement();
					//执行sql命令
					int row=st.executeUpdate(sql);
					System.out.println(row>0?"删除成功":"删除失败");
				} catch (SQLException e) {
					e.printStackTrace();
				}finally {
					if (st!=null) {
						try {
							st.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					if (conn!=null) {
						try {
							conn.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				}
			}
			@Test
			public void selet() {
				Connection conn=null;
				String username="root";
				String password="root";
				String driver="com.mysql.jdbc.Driver";
				String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
				try {
					//2.加载驱动到虚拟机中
					Class.forName(driver);//这个是反射
					//DriverManager数据库连接
					conn=DriverManager.getConnection(url,username,password);
				} catch (Exception e) {
					e.printStackTrace();
				}
				//准备SQL命令
				String sql="select * from Pms_album";
				Statement st=null;
				ResultSet rs=null;
				try {
					//通过数据库连接conn获取事务java.sql.Statement对象st
					st=conn.createStatement();
					//执行sql命令
					rs=st.executeQuery(sql);
					//处理结果集
					while (rs.next()) {
						//通过columnLabel列名或者别名获取,
						System.out.println(rs.getInt("id"));
						System.out.println(rs.getObject("name"));
						System.out.println(rs.getObject("cover_pic"));
						System.out.println(rs.getObject("pic_count"));
						System.out.println(rs.getObject("sort"));
						System.out.println(rs.getObject("description"));
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}finally {
					if (st!=null) {
						try {
							st.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					if (rs!=null) {
						try {
							rs.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					if (conn!=null) {
						try {
							conn.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				}
				
				
			}


}
